from flask import Flask, request, render_template_string
from datetime import datetime,timedelta
import os
from sqlalchemy import create_engine,text
import json
import re
from ott_analyse import extract_timestamps_from_filenames
engine = create_engine(
    'mysql+mysqlconnector://root:Bz_202501@bj-cdb-ckq2r8ro.sql.tencentcdb.com:25622/bz_system',
    pool_recycle=3600,
    echo=False,  # 将echo从True改为False以关闭SQL日志
    isolation_level="READ COMMITTED",
    pool_pre_ping=True
)
app = Flask(__name__)

# 简单的HTML模板
HTML = """
<!doctype html>
<title>手机号查询</title>
<h2>请输入手机号：</h2>
<form method="post">
  <input type="text" name="phone" required>
  <input type="submit" value="查询">
</form>
{% if result %}
  <h3>查询结果：</h3>
  <p>{{ result }}</p>
{% endif %}
"""

@app.route('/', methods=['GET', 'POST'])
def query():
    root_path = '/mnt/ott_analysis'
    Day3_date  = (datetime.now()-timedelta(days=6)).strftime('%Y-%m-%d')

    data_list = []
    



    result = None
    if request.method == 'POST':
        phone = request.form.get('phone')
        sql = text("SELECT profile_id FROM sys_user WHERE mobile = :phone LIMIT 1")
        with engine.connect() as conn:
            row = conn.execute(sql, {'phone': phone}).fetchone()
            if row:
                profile_id=row[0]
            else:
                result = f"未找到手机号 {phone} 对应的 profile_id"
                return render_template_string(HTML, result=result)

        

        time_list = []
        for date_dir in os.listdir(root_path):
            if date_dir < Day3_date:
                continue
            user_path = os.path.join(root_path, date_dir, str(profile_id))
            if not os.path.exists(user_path):
                result = f"{date_dir}未找到用户 {phone} 数据传输"
                time_list.append(result)
                continue
            for file_name in os.listdir(user_path):
                file_path = os.path.join(user_path, file_name)
                with open(file_path, 'r') as f:
                    try:
                        data = json.load(f)
                        timestamps = extract_timestamps_from_filenames(data)
                        time_list.extend(timestamps)
                    except json.JSONDecodeError as e:
                        result = f"程序有问题，找先火问问：文件 {file_name} 解析错误: {str(e)}"
                        return render_template_string(HTML, result=result)
        time_list = list(set(time_list))
        time_list.sort()
        return render_template_string(HTML, result=f"手机号 {phone} ,profile_id:{profile_id}，数据传输的时间戳列表：{time_list}")



        
    return render_template_string(HTML, result=result)

if __name__ == '__main__':
    app.run(debug=True, host='0.0.0.0', port=5000)